/**
 * 
 */
package com.sino.province.service;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

/**
 * @author matianq
 *
 */
@Service
public class ProvinceService {

	@Autowired
	private DruidDataSource dataSource;

	/**
	 * 查询省情况
	 * @return
	 */
	public List<Map<String, Object>> getProvinceMap(String type,String province) {
		String sql = "select province,EXTEND02 as shi,count(1) as jyl from TD_DATA where SUB_TRANS_TYPE='" + type
				+ "' and PROVINCE='" + province
				+ "' and EXTEND02 is NOT NULL and trunc(ts)=trunc(sysdate) and ret_code='True' group by province,EXTEND02";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询省销量top10
	 * @return
	 */
	public List<Map<String, Object>> getProvinceTopTen(String type,String province) {
		String sql = "select shi,jyl,rownum from (select EXTEND02 as shi,count(1) as jyl from TD_DATA where province='"
				+ province + "' and SUB_TRANS_TYPE='" + type
				+ "' AND EXTEND02 is not NULL and trunc(ts)=trunc(sysdate) and ret_code='True' group by EXTEND02 order by JYL desc) where rownum<=10";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询省内实时交易量
	 * @return
	 */
	public List<Map<String, Object>> getProvinceRealTime(String type,String province) {
		StringBuilder sb = new StringBuilder();
		sb.append("select HH,COUNT(1) AS JYL FROM  ");
		sb.append("( select   to_char(ts, 'hh24')||':'|| decode( floor(to_char(ts, 'mi') / 5) * 5,0,'00',5,'05',floor(to_char(ts, 'mi') / 5) * 5)  as HH from td_data t where 1=1");
		sb.append(" and trunc(ts)=trunc(sysdate) ");
		if (type != null && !"".equals(type))
			sb.append(" and sub_trans_type ='" + type + "' ");
		sb.append(" and clientip is not null ");
		sb.append(" and province ='" + province + "'");
		sb.append(" and ret_code='True' )");
		sb.append(" GROUP BY HH ORDER BY HH ");
		String sql = sb.toString();
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询省交易种类top10
	 * @return
	 */
	public List<Map<String, Object>> provinceTypeTopTen(String type,String province) {
		String sql = "SELECT * from (select EXTEND01 as TRANS_TYPE,count(1) as jyl from TD_DATA where PROVINCE='"
				+ province + "' and SUB_TRANS_TYPE='" + type
				+ "' and EXTEND01 is not null and trunc(ts)=trunc(sysdate) and ret_code='True' group by EXTEND01 ORDER BY jyl DESC) WHERE ROWNUM<=10";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询省交易总量
	 * @return
	 */
	public List<Map<String, Object>> provinceSaleAmount(String type,String province) {
		String sql = "SELECT count(1) jshu,round(sum(payamt)/10000,2) sumpayamt from TD_DATA where PROVINCE='"
				+ province + "' and SUB_TRANS_TYPE='" + type + "' and trunc(ts)=trunc(sysdate) and ret_code='True'";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	
	/**
	 * runSql
	 * @param sql
	 * @return
	 */
	public List<Map<String, Object>> execSql(String sql) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			DruidPooledConnection conn = dataSource.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData();
			// 得到结果集(rs)的结构信息，比如字段数、字段名等
			int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
			Map<String, Object> rowData = new HashMap<String, Object>();
			while (rs.next()) {
				rowData = new HashMap<String, Object>(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
			rs.close();
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   
		return list;
	}
}
